﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using MOD;

namespace DOM
{
    public class CourseDOM
    {
        //插入课程
        public bool InsertCourse(string courseId, string courseName, string credit)
        {
           // int i = 0;
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
            try
            {
                con.Open();
                OleDbCommand cmd = con.CreateCommand();
                cmd.CommandText = "INSERT INTO course (courseId,courseName,credit) VALUES (@courseId,@courseName,@credit)";
                cmd.Parameters.AddWithValue("@courseId", courseId);
                cmd.Parameters.AddWithValue("@courseName", courseName);
                cmd.Parameters.AddWithValue("@credit", credit);
                cmd.ExecuteNonQuery();
                con.Close();
                return true;
            }
            catch (Exception)
            {
               // MessageBox.Show(" database error!" + e.Message);
                return false;

            }


        }
        //查询所有课程
        public DataTable SelectAll()
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
           
            DataTable dt = new DataTable();

            try
            {
                con.Open();
                string queryString = "SELECT * FROM course ";
                OleDbDataAdapter thisAdapter = new OleDbDataAdapter(queryString, con);
                
                thisAdapter.Fill(dt);
                con.Close();
                
                return dt;
            }
            catch (Exception )
            {
                //MessageBox.Show(e.Message);
                return dt;
            }

        }
        //通过课程号查询
        public DataTable SelectById(string Id)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
            con.Open();
            string queryStr = "SELECT * FROM course WHERE courseId = @Id";
            OleDbDataAdapter result = new OleDbDataAdapter(queryStr, con);
            result.SelectCommand.Parameters.AddWithValue("@Id",Id);
            DataTable dt = new DataTable();
            result.Fill(dt);
            con.Close();
            return dt;
        }
        /// <summary>
        /// 通过课程名查询
        /// </summary>
        /// <param name="Name"></param>
        /// <returns></returns>
        public DataTable SelectByCourseName(string Name)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
            con.Open();
            string queryStr = "SELECT * FROM course WHERE courseName=@Name";
            OleDbDataAdapter result = new OleDbDataAdapter(queryStr, con);
            result.SelectCommand.Parameters.AddWithValue("@Name", Name);
            DataTable dt = new DataTable();
         
            result.Fill(dt);
            return dt;
            
        }
        //删除操作
        public bool DelCourse(string Id)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
            try
            {
                con.Open();
                string queryStr = "DELETE * FROM  course WHERE courseId=@Id";
                OleDbCommand cmd = new OleDbCommand();

                cmd.Connection = con;
                cmd.CommandText = queryStr;
                cmd.Parameters.AddWithValue("@Id", Id);
                cmd.ExecuteNonQuery();
                con.Close();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        // 修改操作
        public bool UpdateCourse(string Id, string name, string credit)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
            //try
            //{
                con.Open();
                string queryStr = "UPDATE course SET courseId=@courseId,courseName=@courseName, credit =@credit WHERE courseId= @courseId";
                OleDbCommand cmd =new OleDbCommand();
                cmd.Connection = con;
                cmd.CommandText = queryStr;
                cmd.Parameters.AddWithValue("@courseId", Id);
                cmd.Parameters.AddWithValue("@courseName", name);
                cmd.Parameters.AddWithValue("@credit", credit);
                cmd.ExecuteNonQuery();
                con.Close();
                return true;


            //}
            //catch
            //{
            //    return false;
            //}
        }
        public List<CourseInfo> SelectList(List<string> idList)
        {
            List<CourseInfo> info = new List<CourseInfo>();
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
            string queryString = "SELECT * FROM course WHERE courseId=@courseId";
            con.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = con;
            cmd.CommandText = queryString;
            for (int i = 0; i < idList.Count; i++)
            {
                cmd.Parameters.AddWithValue("@courseId", idList[i]);
                OleDbDataReader reader = cmd.ExecuteReader();
                info.Add(new CourseInfo
                                        (
                                            (string) reader["courseId"],
                                            (string)reader["courseName"],
                                            (string)reader["credit"]

                                        )
                );
                
            }
            con.Close();
            return info;
            
        }
    }
}
 